/**
 * model/sqlite/deviceBorrow.js
 */

const sqlite3 = require("sqlite3").verbose();
const config = require("../../common/config");
const util = require("../../common/util");

class BorrowDB {
  constructor() {
    this.dbFile = config.dbfile;
    this.instance = null;
    this.db = null;
  }

  static getInstance() {
    if (!this.instance) {
      this.instance = new BorrowDB();
    }
    return this.instance;
  }

  connect() {
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbFile, (err) => {
        if (err) reject(err);
        else resolve("connect ok.");
      });
    });
  }

  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) reject(err);
        else resolve("close ok.");
      });
    });
  }

  /**
   * 查询单条借用记录，连带设备和用户信息
   * @param {number} id
   */
  find(id) {
    const sql = `
      SELECT br.*, d.title AS device_title, d.status AS device_status, u.username 
      FROM borrow_records br
      JOIN lab_device d ON br.device_id = d.id
      JOIN user u ON br.user_id = u.id
      WHERE br.id = ?
    `;
    return new Promise((resolve, reject) => {
      this.db.get(sql, [id], (err, row) => {
        if (err) reject(err);
        else resolve(row);
      });
    });
  }

  /**
   * 查询借用记录列表，支持分页，支持按status/userId/deviceId过滤，连带设备和用户信息
   * @param {object} params {limit, offset, filters}
   */
  findAll({ limit = -1, offset = -1, filters = {} } = {}) {
    let sql = `
      SELECT br.*, d.title AS device_title, d.status AS device_status, u.username 
      FROM borrow_records br
      JOIN lab_device d ON br.device_id = d.id
      JOIN users u ON br.user_id = u.id
      WHERE 1=1
    `;
    const params = [];

    if (filters.status) {
      sql += " AND br.status = ?";
      params.push(filters.status);
    }
    if (filters.userId) {
      sql += " AND br.user_id = ?";
      params.push(filters.userId);
    }
    if (filters.deviceId) {
      sql += " AND br.device_id = ?";
      params.push(filters.deviceId);
    }

    sql += " ORDER BY br.id DESC";

    if (limit > 0) {
      sql += " LIMIT ?";
      params.push(limit);
    }
    if (limit > 0 && offset >= 0) {
      sql += " OFFSET ?";
      params.push(offset);
    }
    console.log('SQL:', sql);
    console.log('Params:', params);

    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  }

  /**
   * 新建设备借用记录（借用），带设备状态检查、用户借用限制
   * @param {number} userId 
   * @param {number} deviceId 
   */
  async createBorrowRecord(userId, deviceId) {
    const now = new Date().toISOString();

    // 检查设备是否存在且状态可借用
    const device = await new Promise((resolve, reject) => {
      this.db.get("SELECT * FROM lab_device WHERE id = ?", [deviceId], (err, row) => {
        if (err) reject(err);
        else resolve(row);
      });
    });
    if (!device) throw new Error("设备不存在");
    // if (device.status === "fault") throw new Error("设备故障，无法借用");
    if (device.status === "borrowed") throw new Error("设备已被借用");
    if (device.status === "故障") util.err("设备故障，暂不支持借用");
    // 判断用户当前借用数是否超过2台
    const countRow = await new Promise((resolve, reject) => {
      this.db.get(
        "SELECT COUNT(*) AS c FROM borrow_records WHERE user_id = ? AND status = 'borrowed'",
        [userId],
        (err, row) => {
          if (err) reject(err);
          else resolve(row);
        }
      );
    });
    if (countRow.c >= 2) throw new Error("用户最多只能借用两台设备");

    // 开启事务，插入借用记录并更新设备状态
    return new Promise((resolve, reject) => {
      this.db.serialize(() => {
        this.db.run("BEGIN TRANSACTION");
        this.db.run(
          `INSERT INTO borrow_records (user_id, device_id, borrow_time, status, created_time, updated_time)
           VALUES (?, ?, ?, 'borrowed', ?, ?)`,
          [userId, deviceId, now, now, now],
          function (err) {
            if (err) {
              this.db.run("ROLLBACK");
              return reject(err);
            }

            const borrowId = this.lastID;
            // 更新设备状态
            this.db.run(
              `UPDATE lab_device SET status = 'borrowed' WHERE id = ?`,
              [deviceId],
              (err2) => {
                if (err2) {
                  this.db.run("ROLLBACK");
                  return reject(err2);
                }
                this.db.run("COMMIT");
                resolve(borrowId);
              }
            );
          }.bind(this)
        );
      });
    });
  }

  /**
   * 归还设备
   * @param {number} borrow_id
   */
  // async returnBorrowRecord(userId,deviceId) {
  //   const now = new Date().toISOString();

  //   const borrow = await new Promise((resolve, reject) => {
  //     this.db.get(
  //       "SELECT * FROM borrow_records WHERE device_id = ? AND user_id=? AND status = 'borrowed'",
  //       [deviceId,userId],
  //       (err, row) => {
  //         if (err) reject(err);
  //         else resolve(row);
  //       }
  //     );
  //   });
  //   if (!borrow) util.err("未找到可归还的借用记录");

  //   // 开启事务，更新借用记录和设备状态
  //   return new Promise((resolve, reject) => {
  //     this.db.serialize(() => {
  //       this.db.run("BEGIN TRANSACTION");
  //       this.db.run(
  //         `UPDATE borrow_records SET return_time = ?, status = 'returned', updated_time = ? WHERE device_id = ? AND user_id = ?`,
  //         [now, now, deviceId,userId],
  //         (err) => {
  //           if (err) {
  //             this.db.run("ROLLBACK");
  //             return reject(err);
  //           }
  //           this.db.run(
  //             `UPDATE lab_device SET status='启用' WHERE id = ? AND status='borrowed'`,
  //             [deviceId],
  //             (err2) => {
  //               if (err2) {
  //                 this.db.run("ROLLBACK");
  //                 return reject(err2);
  //               }
  //               this.db.run("COMMIT");
  //               resolve(true);
  //             }
  //           );
  //         }
  //       );
  //     });
  //   });
  // }


  async returnBorrowRecord(userId, deviceId) {
    const now = new Date().toISOString();
  
    const borrow = await new Promise((resolve, reject) => {
      this.db.get(
        "SELECT * FROM borrow_records WHERE device_id = ? AND user_id=? AND status = 'borrowed'",
        [deviceId, userId],
        (err, row) => {
          if (err) reject(err);
          else resolve(row);
        }
      );
    });
  
    if (!borrow) {
      util.err("未找到可归还的借用记录");
    }
  
    return new Promise((resolve, reject) => {
      let borrowChanges = 0;
      let deviceChanges = 0;
  
      this.db.serialize(() => {
        this.db.run("BEGIN TRANSACTION");
        this.db.run(
          `UPDATE borrow_records SET return_time = ?, status = 'returned', updated_time = ? WHERE device_id = ? AND user_id = ?`,
          [now, now, deviceId, userId],
          function (err) {
            if (err) {
              this.db.run("ROLLBACK");
              return reject(err);
            }
            borrowChanges = this.changes; // 本语句影响的行数
  
            // 下一条更新语句
            // 注意这里不要用箭头函数，否则this无法访问
            this.db.run(
              `UPDATE lab_device SET status='启用' WHERE id = ? AND status='borrowed'`,
              [deviceId],
              function (err2) {
                if (err2) {
                  this.db.run("ROLLBACK");
                  return reject(err2);
                }
                deviceChanges = this.changes;
  
                this.db.run("COMMIT", (commitErr) => {
                  if (commitErr) return reject(commitErr);
                  // 返回两次更新影响的行数之和
                  resolve(borrowChanges + deviceChanges);
                });
              }.bind(this)
            );
          }.bind(this)
        );
      });
    });
  }
}
module.exports = BorrowDB;


